package com.cloudea.lotus.dao;

import com.cloudea.lotus.Response;
import com.cloudea.lotus.bo.Column;
import com.cloudea.lotus.bo.Role;
import com.cloudea.lotus.bo.UserInfo;
import com.cloudea.lotus.utils.StringUtil;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonPrimitive;
import com.mysql.cj.conf.ConnectionUrlParser.Pair;

import org.apache.log4j.Logger;

import java.sql.*;
import java.util.*;

public class MySQLDAO implements DAO {
    private Logger logger = Logger.getLogger(this.getClass());
    private Connector connector;
    private Map<String, Map<String, Column>> tables;  // read only
    private final int MAX_LIMIT = 1000;        //查询的最大个数

    public MySQLDAO(Connector connector){
        this.connector = connector;
        // 获取元信息
        tables = new HashMap<>();
        Connection connection = null;
        try {
            logger.info("正在获取所有的表...");
            connection = connector.connect();
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet rs = metaData.getTables(null, connection.getSchema(), "%", null);
            while (rs.next()){
                String tableName = rs.getString("TABLE_NAME");
                Map<String, Column> columns = new HashMap<>();
                //列取表的列
                try{
                    ResultSet resultSet = metaData.getColumns(null, connection.getSchema(), tableName, "%");
                    while (resultSet.next()){
                        Column column = new Column();
                        column.setName(resultSet.getString("COLUMN_NAME"));
                        column.setType(resultSet.getInt("DATA_TYPE"));
                        column.setAutoIncrement(resultSet.getString("IS_AUTOINCREMENT").equals("YES"));
                        columns.put(column.getName(), column);
                    }
                    //id、create_id, update_id
                    Column id = columns.get("id");
                    Column createId = columns.get("create_id");
                    Column updateId = columns.get("update_id");
                    Column roleId = columns.get("object_role");
                    if(id == null || !isValidType(id.getType()) || !id.isAutoIncrement()){
                        continue;
                    }
                    if(createId == null || !isValidType(createId.getType())){
                        continue;
                    }
                    if(updateId == null || !isValidType(updateId.getType())){
                        continue;
                    }
                    if(roleId == null || !isValidType(roleId.getType())){
                        continue;
                    }
                    tables.put(tableName, columns);
                    logger.info("读取表" + tableName + "元数据完毕");
                }catch (Exception e){
                    e.printStackTrace();
                    logger.error("读取表" + tableName + "元数据失败");
                }
            }
            logger.info("获取所有的表完毕");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //即时地归还连接
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                logger.error("关闭连接异常", throwables);
            }
        }
    }

    @Override
    public JsonObject select(String object, String id) {
        logger.info(StringUtil.toTip("GET", "/" + object + "/" + id, null));
        Connection connection = null;
        try {
            connection = connector.connect();
            String templace = "select * from %s where id = %s";
            String sql = String.format(templace, object, id);
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            if(resultSet.next()){
                Map<String, Column> columns = tables.get(object);
                JsonObject jsonObject = new JsonObject();
                for (String key : columns.keySet()){
                    Column column = columns.get(key);
                    jsonObject.addProperty(key, StringUtil.toString(resultSet, key, column.getType()));
                }
                connection.close();
                return jsonObject;
            }
        } catch (SQLException throwables) {
            logger.error("查询出现错误", throwables);
        }
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                logger.error( "归还连接出现异常",e);
            }
        }
        return null;
    }

    @Override
    public JsonElement select(String object, Map<String, String> queryParameters) {
        logger.info(StringUtil.toTip("GET", "/" + object, queryParameters));
        Connection connection = null;
        try {
            connection = connector.connect();
            String templateForCount = "select count(*) as num from %s where %s";
            String templateForQuery = "select * from %s where %s %s limit %s, %s";
            // 计数参数
            boolean countMode = queryParameters.get("count") != null && queryParameters.get("count").equals("1");
            queryParameters.remove("count");

            // 分页参数
            int offset = 0;
            int limit = MAX_LIMIT;
            if(queryParameters.get("offset") != null){
                offset = Integer.parseInt(queryParameters.get("offset"));
                if(offset < 0){
                    offset = 0;
                }
            }
            if(queryParameters.get("limit") != null){
                limit = Integer.parseInt(queryParameters.get("limit"));
                if(limit < 0){
                    offset = 0;
                }else if(limit > MAX_LIMIT){
                    offset = MAX_LIMIT;
                }
            }
            queryParameters.remove("offset");
            queryParameters.remove("limit");

            //sort statement & query statement
            StringBuilder queryStatement = new StringBuilder("1=1 ");
            List<String> queryKeys = new LinkedList<>();

            PriorityQueue<Pair<String, Integer>> sortKeys = new PriorityQueue<>((p1, p2) -> p1.right- p2.right);
            for(String key : queryParameters.keySet()){
                String[] frags = key.split("\\.", 2);
                if(frags.length == 1){
                    Column column = tables.get(object).get(frags[0]);
                    if(column != null){
                        int type = column.getType();
                        boolean isValidString = isString(type) || isNString(type);
                        if(isValidString){
                            queryStatement.append("and " + frags[0] + " like ? ");
                        }else {
                            queryStatement.append("and " + frags[0] + " = ? ");
                        }
                        queryKeys.add(key);
                    }
                }else if(frags[1].equals("eq")){
                    queryStatement.append("and " + frags[0] + " = ? ");
                    queryKeys.add(key);
                }else if(frags[1].equals("neq")){
                    queryStatement.append("and " + frags[0] + " != ? ");
                    queryKeys.add(key);
                }else if(frags[1].equals("gt")){
                    queryStatement.append("and " + frags[0] + " > ? ");
                    queryKeys.add(key);
                }else if(frags[1].equals("ge")){
                    queryStatement.append("and " + frags[0] + " >= ? ");
                    queryKeys.add(key);
                }else if(frags[1].equals("lt")){
                    queryStatement.append("and " + frags[0] + " < ? ");
                    queryKeys.add(key);
                }else if(frags[1].equals("le")){
                    queryStatement.append("and " + frags[0] + " <= ? ");
                    queryKeys.add(key);
                }else if(frags[1].equals("asc")){
                    String value = queryParameters.get(key);
                    int rank = Integer.parseInt(value);
                    sortKeys.add(new Pair<>(key, rank));
                }else if(frags[1].equals("desc")){
                    String value = queryParameters.get(key);
                    int rank = Integer.parseInt(value);
                    sortKeys.add(new Pair<>(key, rank));
                }
            }
            // 构造排序子句
            StringBuilder sortStatement = new StringBuilder("");
            if(!sortKeys.isEmpty()){
                sortStatement.append("order by ");
                while (!sortKeys.isEmpty()){
                    String[] frags = sortKeys.poll().left.split("\\.", 2);
                    logger.trace("正在解析排序选项：" + frags[0] + " " + frags[1]);
                    sortStatement.append(frags[0]);
                    sortStatement.append(" ");
                    sortStatement.append(frags[1]);
                    if(!sortKeys.isEmpty()){
                        sortStatement.append(",");
                    }
                }
            }

            // 构造用于查询的sql
            String sql = countMode
                    ? String.format(templateForCount, object, queryStatement)
                    : String.format(templateForQuery, object, queryStatement, sortStatement.toString(), offset, limit) ;
            logger.debug(sql);

            // 为空白处赋值
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            int index = 1;
            for(String queryKey : queryKeys){
                String key = queryKey.split("\\.", 2)[0];
                Column column = tables.get(object).get(key);
                if(column != null) {
                    String value = queryParameters.get(queryKey);
                    int type = column.getType();
                    StringUtil.toColumn(preparedStatement, index, value, type);
                }
                index += 1;
            }

            // 结果处理
            logger.debug("即将执行SQL：" + preparedStatement.toString());
            ResultSet resultSet = preparedStatement.executeQuery();
            if(countMode){
                if(resultSet.next()){
                    int count = resultSet.getInt("num");
                    connection.close();
                    return new JsonPrimitive(count);
                }else {
                    connection.close();
                    return null;
                }
            }else{
                JsonArray jsonArray = new JsonArray();
                while (resultSet.next()){
                    JsonObject jsonObject = new JsonObject();
                    Map<String, Column> columns = tables.get(object);
                    for(String key : columns.keySet()){
                        String value = StringUtil.toString(resultSet, key, columns.get(key).getType());
                        jsonObject.addProperty(key, value);
                    }
                    jsonArray.add(jsonObject);
                }
                connection.close();
                return jsonArray;
            }
        } catch (Exception throwables) {
            logger.error("查询出现错误", throwables);
        }

        if(connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                logger.error("连接关闭失败", throwables);
            }
        }
        return null;
    }

    @Override
    public String insert(String object, Map<String, String> queryParameters) {
        logger.info(StringUtil.toTip("POST", "/" + object, queryParameters));
        Connection connection = null;
        try {
            connection = connector.connect();
            connection.setAutoCommit(false);
            String sqlTemplate = "insert into %s(%s) values(%s)";
            String columns = String.join(",", queryParameters.keySet());
            String values = String.join(",", generatePlaces(queryParameters.size()));
            String sql = String.format(sqlTemplate, object, columns, values);
            PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            int index = 1;
            for(String column : queryParameters.keySet()){
                Column columnObject = tables.get(object).get(column);
                if(columnObject != null){
                    int type = columnObject.getType();
                    String value = queryParameters.get(column);
                    StringUtil.toColumn(statement, index, value, type);
                }else{
                    String info = String.format("表%s中不存在键值%s", object, column);
                    logger.error(info);
                    connection.close();
                    return null;
                }
                index += 1;
            }
            logger.debug("执行插入操作之前");
            statement.execute();
            logger.debug("执行插入操作之后");
            ResultSet resultSet = statement.getGeneratedKeys();
            String generatedKey = null;
            while (resultSet.next()){
                if(tables.get(object).get("id").getType() == Types.INTEGER){
                    generatedKey = String.valueOf(resultSet.getInt(1));
                }else if(tables.get(object).get("id").getType() == Types.BIGINT){
                    generatedKey = String.valueOf(resultSet.getLong(1));
                }
            }
            connection.commit();
            statement.close();
            connection.setAutoCommit(true);
            connection.close();
            logger.trace( "插入" + object + "结果：" + generatedKey);
            return generatedKey;
        } catch (SQLException throwables) {
            logger.error("插入数据出现异常", throwables);
            if(connection != null){
                try {
                    connection.setAutoCommit(true);
                } catch (SQLException e) {
                    logger.error("设置AutoCommit出现异常", e);
                }
                try {
                    connection.close();
                } catch (SQLException e) {
                    logger.error("连接关闭出现异常", e);
                }
            }
            return null;
        }
    }

    @Override
    public boolean update(String object, String id, Map<String, String> queryParameters) {
        logger.info(StringUtil.toTip("PUT", "/" + object + "/" + id, queryParameters));
        Connection connection = null;
        try {
            connection = connector.connect();
            String template = "update %s set %s where id = %s";
            StringBuilder keyvals = new StringBuilder();
            int index = 1;
            for(String key : queryParameters.keySet()){
                keyvals.append(key);
                keyvals.append("=?");
                if(index != queryParameters.size()){
                    keyvals.append(",");
                }
                index += 1;
            }
            String sql = String.format(template, object, keyvals.toString(), id);
            PreparedStatement statement =  connection.prepareStatement(sql);
            int index2 = 1;
            for(String key : queryParameters.keySet()){
                Column column = tables.get(object).get(key);
                if(column != null){
                    int type = column.getType();
                    String value = queryParameters.get(key);
                    StringUtil.toColumn(statement, index2, value, type);
                }
                index2 += 1;
            }
            logger.debug("执行更新操作之前");
            statement.execute();
            logger.debug("执行更新操作之后");
            statement.close();
            connection.close();
            return true;
        } catch (SQLException throwables) {
            logger.error("更新记录失败", throwables);
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    logger.error("关闭连接出现异常", e);
                }
            }
            return false;
        }
    }

    @Override
    public boolean delete(String object, String id) {
        logger.info(StringUtil.toTip("DELETE", "/" + object + "/" + id, null));
        Connection connection = null;
        try {
            connection = connector.connect();
            String templace = "delete from %s where id = %s";
            String sql = String.format(templace, object, id);
            PreparedStatement statement = connection.prepareStatement(sql);
            boolean isDeleteSucess = statement.executeUpdate() == 1;
            connection.close();
            return isDeleteSucess;
        } catch (SQLException throwables) {
            logger.error("删除记录失败", throwables);
        }
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                logger.error("关闭连接出现异常", throwables);
            }
        }
        return false;
    }


    @Override
    public boolean has(String object) {
        return tables.get(object) != null;
    }

    @Override
    public boolean execute(String sqlBatch) {
        Connection connection  = null;
        try {
            connection = connector.connect();
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            String[] sqls = sqlBatch.split(";");
            for(String sql : sqls){
                statement.addBatch(sql);
            }
            statement.executeBatch();
            statement.close();
            connection.commit();
            connection.setAutoCommit(true);
            connection.close();
            return true;
        } catch (Exception throwables) {
            logger.error("SQL 语句执行失败", throwables);
        }
        if(connection != null){
            try {
                connection.setAutoCommit(true);
                connection.close();
            } catch (SQLException throwables) {
                logger.error("关闭连接出现异常", throwables);
            }
        }
        return false;
    }

    @Override
    public List<String> getTables() {
        return List.copyOf(tables.keySet());
    }

    @Override
    public UserInfo login(String account, String password) {
        String tip = String.format("account = %s, password = %s", account, password);
        logger.info("用户登录：" + tip);
        Connection connection = null;
        try {
            connection = connector.connect();
            UserInfo userInfo = new UserInfo();

            //根据是否匿名决定user的id
            if(account == null && password == null || account.equals("") && password.equals("")){
                userInfo.setUserId("0");
                // 需要手动添加0号用户的角色和角色权限，就不默认生成了。
            }else{
                PreparedStatement statementLogin = connection.prepareStatement("select id from _user where account = ? and password = ?");
                statementLogin.setString(1, account);
                statementLogin.setString(2, password);
                ResultSet resultSet = statementLogin.executeQuery();
                if(resultSet.next()){
                    String userId = StringUtil.toString(resultSet, "id", tables.get("_user").get("id").getType());
                    userInfo.setUserId(userId);
                }else{
                    throw  new RuntimeException("没有找到此用户：" + tip);
                }
            }

            //根据用户id查找role
            Set<String> roles = new HashSet<>();
            Queue<Role> queue = new ArrayDeque<>();
            String sql = "select _role.id as id, _role.parent_id as parent_id " +
                    "from _user_role inner join _role on _user_role.role_id = _role.id  where _user_role.user_id = ?";
            PreparedStatement statementRole = connection.prepareStatement(sql);
            statementRole.setString(1, userInfo.getUserId());
            ResultSet roleResult = statementRole.executeQuery();
            while (roleResult.next()){
                String roleId = StringUtil.toString(roleResult, "id", tables.get("_role").get("id").getType());
                String parentId = StringUtil.toString(roleResult, "parent_id", tables.get("_role").get("parent_id").getType());
                Role role = new Role();
                role.setRoleId(roleId);
                role.setParentId(parentId);
                queue.add(role);
            }

            // * 广度优先遍历
            while (!queue.isEmpty()){
                Role role = queue.poll();
                roles.add(role.getRoleId());
                String sql2 = "select id, parent_id from _role where parent_id = ?";
                PreparedStatement deepSearchStatement = connection.prepareStatement(sql2);
                deepSearchStatement.setString(1, role.getRoleId());
                ResultSet resultSet = deepSearchStatement.executeQuery();
                while(resultSet.next()){
                    String roleId = StringUtil.toString(resultSet, "id", tables.get("_role").get("id").getType());
                    String parentId = StringUtil.toString(resultSet, "parent_id", tables.get("_role").get("parent_id").getType());
                    Role role2 = new Role();
                    role2.setRoleId(roleId);
                    role2.setParentId(parentId);
                    queue.add(role2);
                }
            }

            // 对每个role查找它的权限
            Set<String> permissions =  new HashSet<>();
            for(String roleId : roles){
                String sqlForPerm = "select _tran.object_name as object_name, _tran.operation as operation" +
                        " from _role_tran inner join _tran on  _role_tran.tran_id = _tran.id where _role_tran.role_id = ? ";
                PreparedStatement preparedStatement = connection.prepareStatement(sqlForPerm);
                preparedStatement.setString(1, roleId);
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()){
                    String objectName = StringUtil.toString(resultSet, "object_name", tables.get("_tran").get("object_name").getType());
                    String operation = StringUtil.toString(resultSet, "operation", tables.get("_tran").get("operation").getType());
                    permissions.add(objectName + "_" + operation);
                }
            }

            userInfo.setRoles(List.copyOf(roles));
            userInfo.setPerms(List.copyOf(permissions));
            connection.close();
            return userInfo;
        } catch (Exception throwables) {
            logger.error("登录出现异常", throwables);
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    logger.error("关闭连接出现异常",e);
                }
            }
            return null;
        }
    }

    private boolean isValidType(int type){
        return type == Types.INTEGER || type == Types.BIGINT;
    }

    private boolean isString(int type){
        return type == Types.CHAR || type == Types.VARCHAR || type == Types.LONGVARCHAR;
    }

    private boolean isNString(int type){
        return type == Types.NCHAR || type == Types.NVARCHAR || type == Types.LONGNVARCHAR;
    }

    private String[] generatePlaces(int count){
        if(count < 0){
            count = 0;
        }
        String[] places = new String[count];
        for (int i = 0; i < places.length; i++) {
            places[i] = "?";
        }
        return  places;
    }


}
